#########################
#   Author : Lion yu    #
#   Date : 2023/05/07   #
#   Id : SM2772         #
#########################
import os
import json
import re
import datetime
import time
from datetime import datetime as dt, date, timedelta
from threading import Thread, Lock
from concurrent.futures import ThreadPoolExecutor
import pandas as pd
from handle import Log, Handle, BugBI, ProjectConfig
from tbUtils.TbOpenApis import *

global_bugList = list()

"""
递归获取所有结果集
"""
# 获取所有项目
def allResp_ProjectQuery():
    resp_l = list()
    def resp_ProjectQuery(resp_l:list=[], pageToken="\"\""):
        resp = ProjectQuery(pageToken)
        nextPageToken = str()
        try:
            nextPageToken = resp['nextPageToken']
        except Exception as e:
            Log.error(e)
        resp_l += resp['result']
        if len(nextPageToken) > 0:
            resp_ProjectQuery(pageToken=nextPageToken)
        return resp_l
    resp_l += resp_ProjectQuery()
    resp_df = pd.DataFrame(resp_l)
    df_columns = ['id', 'name', 'description', 'isArchived', 'isSuspended', 'startDate', 'endDate']
    resp_df = resp_df[df_columns]
    resp_df.rename(columns={'id':'projectId'}, inplace=True)
    resp_df.fillna("", inplace=True)
    resp_df['isArchived'] = resp_df['isArchived'].astype('int')
    resp_df['isSuspended'] = resp_df['isSuspended'].astype('int')
    resp_df.set_index('projectId', inplace=True)
    return resp_df

# 获取查询项目任务
def SimpleResp_ProjectTaskQuery(projectId):
    resp_l = list()
    def resp_ProjectTaskQuery(projectId=projectId, pageToken="\"\"", resp_l:list=[]):
        resp = ProjectTaskQuery(projectId=projectId, pageToken=pageToken)
        nextPageToken = str()
        try:
            nextPageToken = f"\"{resp['nextPageToken']}\""
            nextPageToken.replace(" ","")
        except Exception as e:
            Log.error(e)
        if len(nextPageToken) > 2:  # >2 是因为在定义 nextPageToken 变量时，左右两侧增加了""符号，导致就算为空，最短长度也为2
            resp_ProjectTaskQuery(projectId=projectId, pageToken=nextPageToken)
        resp_l += resp['result']
        return resp_l
    resp_fl = resp_ProjectTaskQuery()
    resp_df = pd.DataFrame(resp_fl)
    resp_df['taskId'] = resp_df['id']
    resp_df['name'] = resp_df['content'].apply(lambda x:x.replace("\r",""))
    resp_df.rename(columns={'customfields':'customfields_info'}, inplace=True)
    if 'parentTaskId' not in resp_df.columns:
        resp_df['parentTaskId'] = ""
    resp_df = resp_df[['taskId', 'name', 'projectId', 'parentTaskId', 'tfsId', 'creatorId', 'customfields_info', 'sfcId', 'created']]
    resp_df.fillna("", inplace=True)
    resp_df.drop_duplicates(subset='taskId', keep='last', inplace=True)
    resp_df['customfields_info'] = resp_df['customfields_info'].apply(lambda x:str(x))
    # 兼容【商米安全问题改善】项目
        # 1、发现时间有值则取，无值则取 创建时间（系统自动）
        # 2、关闭时间有值则取，无值则取 完成时间（系统自动)
    if projectId == "64accd09bf9093e620ff5005":
        resp_df['customfields_info_ext'] = resp_df['customfields_info']
        resp_df['created_ext'] = resp_df['created']
        resp_df.fillna({"customfields_info_ext":"[]"},inplace=True)
        def jsonStr_analysis(data:str):
            d = data.replace("\n", "")
            d = json.loads(json.dumps(eval(d)))
            l = [json.loads(json.dumps(i)) for i in d]
            return l
        resp_df['customfields_info_ext'] = resp_df['customfields_info_ext'].apply(jsonStr_analysis)
        # 查表，获取项目bug与sfcId 映射关系
        bugSfcId = "bugSfcId_ext"
        sfcId = Handle.db_select(table=bugSfcId, index="sfcId", where=f"projectId = '{projectId}'")
        sfcId = sfcId[0][0]
        findTime_customfieldId = CustomfieldSearch(projectId=projectId, sfcId=sfcId, q="发现时间")['result'][-1]['id']
        def getFindTime(data):
            findTimeInfo = [i for i in data if i['cfId'] == findTime_customfieldId]
            if len(findTimeInfo) > 0:
                findTime:str = findTimeInfo[-1]['value'][-1]['title']
                return findTime
            else:
                return 0
        resp_df['created'] = resp_df['customfields_info_ext'].apply(getFindTime)
        resp_df.drop("customfields_info_ext", axis=1, inplace=True)
        resp_df.loc[resp_df['created'] == 0, "created"] = resp_df['created_ext']
        resp_df.drop("created_ext", axis=1, inplace=True)
    resp_df.set_index('taskId', inplace=True)
    # 项目任务作为基础数据存储到文件
    taskdata_filepath = os.path.join(Handle.taskData_path, f"{projectId}.pkl")
    resp_df.to_pickle(taskdata_filepath)
    return

# 获取所有项目任务类型
def resp_ScenarioFieldConfigSearch(projectId, pageToken="\"\""):
    resp = ScenarioFieldConfigSearch(projectId, pageToken=pageToken)
    try:
        result = resp['result']
        result = [
            {
                'projectId' : i['boundToObjectId'],
                'task_type' : i['icon'],
                'task_type_id' : i['id'],
                'task_type_name' : i['name']
            } 
            for i in result]
        if len(result) > 0:
            result_df = pd.DataFrame(result)
            result_df.fillna("", inplace=True)
            final_resp = list()
            for i, j in result_df.iterrows():
                final_respMap = {k : v for k, v in j.items()}
                final_resp.append(final_respMap)
            return final_resp
        else:
            return []
    except Exception as e:
        Log.error(e)
        return 

# 获取项目缺陷等级
def SimpleResp_CustomfieldSearch(projectId, sfcId, q):
    result = CustomfieldSearch(projectId=projectId, sfcId=sfcId, q=q)
    result = result['result'][0]
    bugsLevel_info = dict()
    if 'choices' in result:
        bugsLevel_info = {
            i['value']:i['id'] for i in result['choices']
        }
    else:
        Log.error('未找到 bug等级枚举！')
        return
    bugsLevel_info['projectId'] = projectId
    bugsLevel_info['sfcId'] = sfcId
    bugsLevel_info['buglevel_A'] = bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_A']]
    bugsLevel_info['buglevel_B'] = bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_B']]
    bugsLevel_info['buglevel_C'] = bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_C']]
    bugsLevel_info['buglevel_D'] = bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_D']]
    del bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_A']]
    del bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_B']]
    del bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_C']]
    del bugsLevel_info[ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']['buglevel_D']]
    return [bugsLevel_info]

# 获取bug数据快照
def SimpleResp_TaskActivityList(taskId):
    def resp_TaskActivityList(taskId=taskId, pageToken="\"\"", resp_l:list=[]):
        resp_l = resp_l
        resp = TaskActivityList(taskId=taskId, pageToken=pageToken)
        nextPageToken = str()
        try:
            nextPageToken = f"\"{resp['nextPageToken']}\""
            nextPageToken.replace(" ","")
        except Exception as e:
            Log.error(e)
        resp_l += resp['result']
        if len(nextPageToken) > 2:  # >2 是因为在定义 nextPageToken 变量时，左右两侧增加了""符号，导致就算为空，最短长度也为2
            TaskActivityList(taskId=taskId, pageToken=nextPageToken, resp_l=resp_l)
        return resp_l
    resp_fl = resp_TaskActivityList()
    TaskActivity_map = dict()
    TaskActivity_map['reopenCount'] = 0
    oneYearAfter = dt.now().replace(year=dt.now().year+1).strftime("%Y-%m-%d %H:%M:%S")[:10]
    TaskActivity_map['tfsCloseTime'] = Handle.str2datetime(oneYearAfter)
    for i in resp_fl:
        TaskActivity_map['bugId'] = taskId
        TaskActivity_map['tfsname'] = i['action']
        TaskActivity_map['tfsId'] = i['id']
        # 获取创建时间
        if (i['action'] == 'create' or re.search(r"^create.from", i['action'])) and len(i['createTime']) > 0:
            TaskActivity_map['tfsCreateTime'] = Handle.str2datetime(i['createTime'][:10])
        i_content = json.loads(i['content'])
        if 'taskflowstatus' in i_content:
            if i_content['taskflowstatus'] == '作废' or re.search(r"关闭", i_content['taskflowstatus']):
            # in ['关闭', '作废', '问题关闭', '问题解决关闭']
                TaskActivity_map['tfsCloseTime'] = Handle.str2datetime(i['updateTime'][:10])
            if i_content['taskflowstatus'] == '重新打开':
                TaskActivity_map['reopenCount'] += 1
                TaskActivity_map['tfsCloseTime'] = Handle.str2datetime(oneYearAfter)
    if 'tfsCreateTime' not in TaskActivity_map:
        TaskActivity_map['tfsCreateTime'] = ""
    return TaskActivity_map
    
# 项目下所有bug数据切片，提供多线程处理
def Sub_AllTaskActivity(projectId):
    t = Handle.sub_Object(SimpleResp_ProjectBugQuery(projectId=projectId))
    if len(t) == 0:
        Log.warning(f"项目 {projectId} 下暂无BUG")
        return []
    else:
        return t
    
def AllTaskActivity_For_Thread(projectId:str, taskIds:list):
    for i in taskIds:
        try:
            result = SimpleResp_TaskActivityList(i)
            result['projectId'] = projectId
            if result not in global_bugList:
                global_bugList.append(result)
        except Exception as e:
            Log.error(f"taskId = {i} , 数据快照拉取失败")
    return global_bugList

# 多线程 获取bug数据快照
def AllTaskActivity(projectId):
    global global_bugList
    AllTaskActivity_result = Sub_AllTaskActivity(projectId)
    AllTaskActivity_result_1 = AllTaskActivity_result[0]
    AllTaskActivity_result_2 = AllTaskActivity_result[1]
    AllTaskActivity_result_3 = AllTaskActivity_result[2]
    start_time = time.time()
    Log.info(f"===== 多线程 获取projectId:{projectId} bug数据快照 开始\n 预计耗时约 5-10 分钟 =====")
    t1 = Thread(target=AllTaskActivity_For_Thread, args=(projectId, AllTaskActivity_result_1,))
    t2 = Thread(target=AllTaskActivity_For_Thread, args=(projectId, AllTaskActivity_result_2,))
    t3 = Thread(target=AllTaskActivity_For_Thread, args=(projectId, AllTaskActivity_result_3,))
    t1.start(); t2.start(); t3.start()
    t1.join(); t2.join(); t3.join()
    finish_time = time.time()
    consuming_time = int(finish_time - start_time)
    Log.info(f"多线程 获取projectId:{projectId} bug数据快照 结束, 耗时 {consuming_time} 秒")
    bugListDF = pd.DataFrame(global_bugList)
    # 数据去重
    bugListDF.drop_duplicates(subset='bugId', keep='last', inplace=True)
    if "Unnamed: 0" in bugListDF.columns.to_list():
        bugListDF.drop("Unnamed: 0", axis=1, inplace=True)
    # 应用市场-王连清 项目竟然存在 没有创建时间的bug？过滤！
    bugListDF = bugListDF[bugListDF['tfsCreateTime'] != '']
    bugListDF.set_index('bugId', inplace=True)
    # 快照数据 存储静态文件
    bugSnapshotData_filepath = Handle.bugSnapshotData_path
    bugListDF['bugId'] = bugListDF.index
    file_path = os.path.join(bugSnapshotData_filepath, f'{projectId}.pkl')
    bugListDF.to_pickle(file_path)
    return

# 获取项目阶段字段
def resp_CustomfieldSearch(projectId:str):
    # 获取sfcId
    task_types = resp_ScenarioFieldConfigSearch(projectId)
    bug_info = {i['task_type']:i['task_type_id'] for i in task_types if i['task_type_name'] == '缺陷'}
    sfcId = bug_info['bug']
    result = CustomfieldSearch(projectId=projectId, sfcId=sfcId, q="项目阶段")['result']
    try:
        result = [{i['id']:{'name':i['name'], 'choise':i['choices']}} for i in result]
        return result
    except Exception as e:
        return []

# 搜索项目工作流状态
def resp_TaskflowstatusSearch(projectId:str, tfsIds:str):
    resp = TaskflowstatusSearch(projectId=projectId, tfsIds=tfsIds)
    resp = resp['result'][0]
    time.sleep(0.01)    # 防止阿里限流
    return resp['name']

# 记录 项目Bug 与 tfsId、reopenID 映射
def ProjectId_columns(projectId:str):
    # 获取项目Bug基础数据
    bugDataExt_filepath = os.path.join(Handle.bugDataExt_path, f"{projectId}.pkl")
    result:pd.DataFrame = pd.read_pickle(bugDataExt_filepath)
    result.fillna("", inplace=True)
    tfsIds_df = result[['tfsId']].drop_duplicates(subset='tfsId', keep='last')
    tfsIds_df['projectId'] = projectId
    def get_tfsIdName(tfsId):
        return resp_TaskflowstatusSearch(projectId=projectId, tfsIds=tfsId)
    tfsIds_df['tfsId_name'] = tfsIds_df['tfsId'].apply(get_tfsIdName)
    # 记录reopen字段id
    sfcId = result[['sfcId']].drop_duplicates(subset='sfcId', keep='last')
    sfcId.reset_index(drop=True, inplace=True)
    sfcId = sfcId['sfcId'].to_dict()[0]
    reopenId = CustomfieldSearch(projectId=projectId, sfcId=sfcId, q='reopen')['result']
    if len(reopenId) > 0:
        reopenId = reopenId[0]['id']
    else:
        reopenId = [""]
    result = [
            {
                'projectId' : projectId,
                'tfsId' : j.to_dict()['tfsId'],
                'tfsId_name' : j.to_dict()['tfsId_name'],
                'reopenId' : reopenId
            }
            for i, j in tfsIds_df.iterrows()
        ]
    return result

# 查询项目BUG（Bug基础数据处理&文件存储）
def Bugs_BaseData(projectId):
    task_types = resp_ScenarioFieldConfigSearch(projectId)
    bug_info = {i['task_type']:i['task_type_id'] for i in task_types if i['task_type_name'] == '缺陷'}
    task_type_id = bug_info['bug']
    # 获取项目任务基础数据
    taskdata_filepath = os.path.join(Handle.taskData_path, f"{projectId}.pkl")
    resp_fl:pd.DataFrame = pd.read_pickle(taskdata_filepath)
    resp_fl.fillna({"customfields_info":"[]"},inplace=True)
    def jsonStr_analysis(data:str):
        d = data.replace("\n", "")
        d = json.loads(json.dumps(eval(d)))
        l = [json.loads(json.dumps(i)) for i in d]
        return l
    resp_fl['customfields_info'] = resp_fl['customfields_info'].apply(jsonStr_analysis)
    resp_fl['customfields_info_length'] = resp_fl['customfields_info'].apply(lambda x:len(x))
    resp_fl = resp_fl[(resp_fl['sfcId'] == task_type_id) & (resp_fl['customfields_info_length'] > 0)]
    resp_fl['bugType_name'] = ""
    def temporaryFunc_1(data):
        return [k['value'][0] for k in data if len(k['value']) > 0 and k['type'] == 'dropDown']
    resp_fl['bugInfo_tmp1'] = resp_fl['customfields_info'].apply(temporaryFunc_1)
    resp_fl['bugInfo_tmp1_length'] = resp_fl['bugInfo_tmp1'].apply(lambda x:len(x))
    # 过滤脏数据
    resp_fl.drop(resp_fl[resp_fl.bugInfo_tmp1_length.isin([0])].index, inplace=True)
    def temporaryFunc_2(data):
        return [i for i in data if 'id' in i]
    resp_fl['bug_level_info'] = resp_fl['bugInfo_tmp1'].apply(temporaryFunc_2)
    resp_fl['sfcId'] = task_type_id
    def temporaryFunc_3(data):
        result = [i for i in data if i['type'] == "commongroup" and len(i['value']) > 0]
        result_value = [i['value'] for i in result]
        if len(result_value) == 0:
            return "无"
        result_value = result_value[::-1][0]
        result = []
        for i in result_value:
            if ('metaString' in i) and ('title' in i):
                metaStringJson = json.loads(i['metaString'])
                result = metaStringJson['allTitle']
                break
            elif ('metaString' not in i) and ('title' in i):
                result = i['title']
                break
            elif ('metaString' not in i) and ('title' not in i):
                result = '无'
            else:
                result = '无'
        return result
    # 区分metaString和title,扩充bugType_nameExt字段
    resp_fl['bugType_nameExt'] = resp_fl['customfields_info'].apply(temporaryFunc_3)
    resp_fl['bugType_name'] = resp_fl['bugType_nameExt']
    resp_fl.drop(resp_fl[resp_fl.bugType_name.isin([0])].index, inplace=True)
    bug_BaseDatas_filepath = os.path.join(Handle.bugDataExt_path, f"{projectId}.pkl")
    resp_fl['taskId'] = resp_fl.index
    resp_fl.to_pickle(bug_BaseDatas_filepath)
    return
    
# 查询项目BUG
def SimpleResp_ProjectBugQuery(projectId):
    # 获取待处理的bug基础数据
    file_path = os.path.join(Handle.bugDataExt_path, f"{projectId}.pkl")
    Bug_basedata_df:pd.DataFrame = pd.read_pickle(file_path)
    Bug_basedata_df.fillna("", inplace=True)
    
    ProjectId_columns = "projectId_columns_ext"
    bugSfcId = "bugSfcId_ext"
    # 查表，获取项目bug与tfsId、 reopenId映射关系
    tfsIds = Handle.db_select(table=ProjectId_columns, index="*", where=f"projectId = '{projectId}'")
    tfsIds_column = Handle.db_connect(f"show columns from {ProjectId_columns}")
    tfsIds_column = [i[0] for i in tfsIds_column]
    tfsIds_df = pd.DataFrame(tfsIds, columns=tfsIds_column)
    # 获取reopenId
    reopenId = tfsIds_df.loc[:,"reopenId"][0]
    
    # 查表，获取项目bug与sfcId 映射关系
    sfcId = Handle.db_select(table=bugSfcId, index="sfcId", where=f"projectId = '{projectId}'")
    sfcId = sfcId[0][0]
    
    bugs_level = SimpleResp_CustomfieldSearch(projectId=projectId, sfcId=sfcId, q=ProjectConfig[projectId]['ProjectBugsLevel_Map']['levelName'])[0]
    del bugs_level['projectId']; del bugs_level['sfcId']
    bugs_level = {j:i for i, j in bugs_level.items()}
    
    # 获取项目Bug基础数据
    Bugs_df = Bug_basedata_df.merge(tfsIds_df, how="left", on=['projectId', 'tfsId'], suffixes=("_bs", "_tfs"))
    Bugs_df.rename(columns={'tfsId_name':'bug_state'}, inplace=True)
    Bugs_df.fillna({"customfields_info":"[]"},inplace=True)
    def temporaryFunc_list2Json(data):
        if len(data) == 0:
            return []
        d = json.dumps(data)
        return json.loads(d)
    Bugs_df['bugInfo_tmp1'] = Bugs_df['bugInfo_tmp1'].apply(temporaryFunc_list2Json)
    Bugs_df['customfields_info'] = Bugs_df['customfields_info'].apply(temporaryFunc_list2Json)
    def temporaryFunc_1(data):
        result = [k['id'] for k in data if 'id' in k and k['id'] in bugs_level]
        if len(result) > 0:
            return result[0]
        else:
            return 0
    Bugs_df['customfields_id'] = Bugs_df['bugInfo_tmp1'].apply(temporaryFunc_1)
    # 过滤脏数据
    Bugs_df.drop(Bugs_df[Bugs_df.customfields_id.isin([0])].index, inplace=True)
    def temporaryFunc_2(data):
        result = [i for i in data if 'id' in i]
        result = [k['title'] for k in result if k['id'] in bugs_level]
        return result[0]
    Bugs_df['customfields_title'] = Bugs_df['bugInfo_tmp1'].apply(temporaryFunc_2)
    ProjectBugsLevel_info = ProjectConfig[projectId]['ProjectBugsLevel_Enumeration']
    ProjectBugsLevel_info = {j:i for i, j in ProjectBugsLevel_info.items()}
    
    # 根据配置中缺陷等级的枚举范围，过滤掉非法bug等级数据
    ProjectBugsLevel_legal = [key for key, value in ProjectBugsLevel_info.items()]
    Bugs_df = Bugs_df[Bugs_df.customfields_title.isin(ProjectBugsLevel_legal)]
    
    Bugs_df['ProjectBugLevel'] = Bugs_df['customfields_title'].apply(lambda x:ProjectBugsLevel_info[x])
    Bugs_df['Bug_BI'] = Bugs_df['ProjectBugLevel'].apply(lambda x:float(BugBI[x]))
    
    # 获取reopen次数
    def temporaryFunc_3(data):
        result = [i['value'] for i in data if i['type'] == 'number' and len(i['value']) > 0 and i['cfId'] == reopenId]
        if len(result) > 0:
            result = int(result[0][0]['title'])
        else:
            result = 0
        return result
    Bugs_df['reopen'] = Bugs_df['customfields_info'].apply(temporaryFunc_3)
    
    # 获取修复人
    def temporaryFunc_4(data):
        result = [i['value'][0] for i in data if i['type'] == 'lookup' and len(i['value']) > 0]
        title = []
        for i in result:
            i_keys = [k for k, v in i.items()]
            check_iList = ['id', 'title']
            if len(list(set(i_keys)^set(check_iList))) == 0:
                title.append(i['title'])
        if len(title) > 0:
            return title[0]
        else:
            return ""
    Bugs_df['fixer'] = Bugs_df['customfields_info'].apply(temporaryFunc_4)
    # 若reopen次数==0, 则隐藏修复人
    def hid_fixer(reopenCount):
        if reopenCount == 0:
            return ""
    Bugs_df['fixer'] = Bugs_df['reopen'].apply(hid_fixer)
    Bugs_df['CreateTime'] = Bugs_df['created'].apply(lambda x:int(x[:4]+x[5:7]+x[8:10]))
    Project_Stage = ProjectConfig[projectId]['ProjectStage']
    def temporaryFunc_5(data):
        for i, j in Project_Stage.items():
            if data >= j[0] and data <= j[1]:
                return i
        return ""
    Bugs_df['project_stage'] = Bugs_df['CreateTime'].apply(temporaryFunc_5)
    
    Bugs_df = Bugs_df[['projectId', 'taskId', 'CreateTime', 'Bug_BI', 'bug_state', 'project_stage', 'tfsId', 'bugType_name', 'customfields_id', 'customfields_title', 'sfcId', 'reopen', 'fixer']]
    Bugs_df.drop_duplicates(subset='taskId', keep='last', inplace=True)
    # Bug基础数据写入文件
    bugdata_filepath = os.path.join(Handle.bugData_path, f"{projectId}.pkl")
    Bugs_df.to_pickle(bugdata_filepath)
    Bugs_df.set_index('taskId', inplace=True)
    taskIds = Bugs_df.index.to_list()
    taskIds = list(set(taskIds))
    return taskIds
    
# 获取项目 Bug等级
def get_BugSfcId(projectId):
    task_types = resp_ScenarioFieldConfigSearch(projectId)
    bug_info = {i['task_type']:i['task_type_id'] for i in task_types if i['task_type_name'] == '缺陷'}
    task_type_id = bug_info['bug']
    return [
            {
                "projectId" : projectId,
                "sfcId" : task_type_id
            }
        ]

# 获取企业部门列表
def get_OrgDepartments():
    def get_OrgDepartments_ext(pageToken="\"\"", resp_l=[]):
        resp = OrgDepartments(pageToken=pageToken)
        nextPageToken = str()
        try:
            nextPageToken = resp['nextPageToken']
        except Exception as e:
            Log.error(e)
        resp_l += resp['result']
        if len(nextPageToken) > 0:
            get_OrgDepartments_ext(pageToken=nextPageToken, resp_l=resp_l)
        return resp_l
    OrgDepartments_df = pd.DataFrame(get_OrgDepartments_ext())
    OrgDepartments_df = OrgDepartments_df[['deptId', 'name']]
    OrgDepartments_df.set_index('deptId', inplace=True)
    return OrgDepartments_df

# # 获取所有部门成员列表
def get_DepartmentMembers(deptTable="org_departments_ext"):
    get_deptIds_info = Handle.db_select(table=deptTable, index="*")
    df = pd.DataFrame()
    for i in get_deptIds_info:
        deptId = i[1]
        deptName = i[2]
        resp = DepartmentMembers(deptId)['result']
        resp_df = pd.DataFrame(resp)
        resp_df['deptId'] = deptId
        resp_df['deptName'] = deptName
        df = pd.concat([df, resp_df])
    df = df[['deptId', 'deptName', 'name']]
    df.set_index('deptId', inplace=True)
    df.drop_duplicates(subset='name', keep='last', inplace=True)
    return df

# 获取bug数据快照 df处理版 （运行速度差别不大，代码可读性提高）
def SimpleResp_TaskActivityList_tmp(taskId):
    # 快照模板df
    TaskActivity_templateDF = pd.DataFrame([{}],columns=['taskId', 'tfsCreateTime', 'tfsCloseTime', 'reopenCount'])
    TaskActivity_templateDF['taskId'] = taskId
    def resp_TaskActivityList(taskId=taskId, pageToken="\"\"", resp_l:list=[]):
        resp_l = resp_l
        resp = TaskActivityList(taskId=taskId, pageToken=pageToken)
        nextPageToken = str()
        try:
            nextPageToken = f"\"{resp['nextPageToken']}\""
            nextPageToken.replace(" ","")
        except Exception as e:
            Log.error(e)
        resp_l += resp['result']
        if len(nextPageToken) > 2:  # >2 是因为在定义 nextPageToken 变量时，左右两侧增加了""符号，导致就算为空，最短长度也为2
            TaskActivityList(taskId=taskId, pageToken=nextPageToken, resp_l=resp_l)
        return resp_l
    resp_fl = resp_TaskActivityList()
    TaskActivity_df = pd.DataFrame(resp_fl)
    TaskActivity_df['updateTime'] = TaskActivity_df['updateTime'].apply(lambda x:str(x).replace("T", " "))
    TaskActivity_df['updateTime'] = TaskActivity_df['updateTime'].apply(lambda x:str(x).replace("X", "")[:-5])
    TaskActivity_df['updateTime'] = TaskActivity_df['updateTime'].apply(lambda x:dt.strptime(x, '%Y-%m-%d %H:%M:%S'))
    TaskActivity_df = TaskActivity_df.sort_values(by='updateTime')
    # 若该bug没有创建时间，直接忽略
    if TaskActivity_df.iloc[0]['action'] != "create":
        return
    bug_createLine = TaskActivity_df.iloc[0]
    TaskActivity_templateDF['tfsCreateTime'] = bug_createLine['updateTime']
    TaskActivity_df.rename(columns={'id':'tfsId', 'action':'tfsname'}, inplace=True)
    TaskActivity_df['content'] = TaskActivity_df['content'].apply(lambda x:json.loads(x))
    TaskActivity_df['content_hasTaskflowstatus'] = TaskActivity_df['content'].apply(lambda x:1 if 'taskflowstatus' in x else 0)
    TaskActivity_df = TaskActivity_df[TaskActivity_df['content_hasTaskflowstatus'] == 1]
    TaskActivity_df['taskflowstatus'] = TaskActivity_df['content'].apply(lambda x : x['taskflowstatus'])
    oneYearAfter = dt.now().replace(year=dt.now().year+1).strftime("%Y-%m-%d %H:%M:%S")[:10]
    TaskActivity_templateDF['tfsCloseTime'] = Handle.str2datetime(oneYearAfter)
    # 判断该bug是否已被关闭或曾被关闭
    TaskActivity_set = set(TaskActivity_df['taskflowstatus'])
    if len(TaskActivity_set) == 0:
        TaskActivity_templateDF['reopenCount'] = 0
        return TaskActivity_templateDF
    # 判断该bug是否存在reopen
    reopenCount = TaskActivity_df[TaskActivity_df['taskflowstatus']=='重新打开'].shape[0]
    TaskActivity_templateDF['reopenCount'] = reopenCount
    # 获取最后一次关闭、作废时间
    TaskActivityDF_lastLine = TaskActivity_df.iloc[-1]
    last_taskflowstatus = TaskActivityDF_lastLine['taskflowstatus']
    if last_taskflowstatus == '作废' or re.search(r"关闭", last_taskflowstatus):
        last_updateTime = TaskActivityDF_lastLine['updateTime']
        TaskActivity_templateDF['tfsCloseTime'] = last_updateTime
    return TaskActivity_templateDF

# 兼容《商米安全问题改善》项目 获取bug快照
        # 1、发现时间有值则取，无值则取 创建时间（系统自动）
        # 2、关闭时间有值则取，无值则取 完成时间（系统自动)
def TaskActivity2SunmiSafeProject(projectId):
    bugData_filePath = os.path.join(Handle.bugData_path, f"{projectId}.pkl")
    bugBaseData_filePath = os.path.join(Handle.bugDataExt_path, f"{projectId}.pkl")
    bugData_df:pd.DataFrame = pd.read_pickle(bugData_filePath)
    bugBaseData_df:pd.DataFrame = pd.read_pickle(bugBaseData_filePath)
    
    bugData_df['task_id'] = bugData_df['taskId']
    bugBaseData_df['task_id'] = bugBaseData_df['taskId']
    bugData_dfColumns = bugData_df.columns.tolist()
    bugData_dfColumns.append("customfields_info")
    bug_df = pd.merge(bugData_df, bugBaseData_df, how='inner', on=['task_id', 'projectId'], suffixes=("", "_base"))
    bug_df = bug_df[bugData_dfColumns]
    # 等一会在优化↓
    bug_df['sysFinishTime'] = bug_df['taskId'].apply(resp_TaskQuery)
    oneYearAfter = dt.now().replace(year=dt.now().year+1).strftime("%Y-%m-%d")
    bug_df.fillna({'sysFinishTime':oneYearAfter}, inplace=True)
    bug_df['sysFinishTime'] = bug_df['sysFinishTime'].apply(lambda x:f"{str(x)[:10]} 00:00:00")
    # 获取 实际关闭时间
    bugSfcId = "bugSfcId_ext"
    sfcId = Handle.db_select(table=bugSfcId, index="sfcId", where=f"projectId = '{projectId}'")
    sfcId = sfcId[0][0]
    RealCloseTime_customfieldId = CustomfieldSearch(projectId=projectId, sfcId=sfcId, q="实际关闭时间")['result'][-1]['id']
    def setCloseTime(data):
        closeTimeInfo = [i for i in data if i['cfId'] == RealCloseTime_customfieldId]
        closeTime = 0
        if len(closeTimeInfo) > 0:
            closeTime = Handle.str2datetime(closeTimeInfo[-1]['value'][-1]['title'][:10])
        return closeTime
    bug_df['tfsCloseTime'] = bug_df['customfields_info'].apply(setCloseTime)
    bug_df.loc[bug_df['tfsCloseTime'] == 0, "tfsCloseTime"] = bug_df['sysFinishTime']
    bug_df.drop(columns=['sysFinishTime'], inplace=True)
    bug_df['tfsCreateTime'] = bug_df['CreateTime'].apply(lambda x:f"{str(x)[:4]}-{str(x)[4:6]}-{str(x)[6:]} 00:00:00")
    bug_df['reopenCount'] = 0
    bug_df['tfsname'] = ""
    bug_df.rename(columns={'taskId':'bugId'}, inplace=True)
    columns = ['reopenCount', 'tfsCloseTime', 'tfsname', 'tfsId', 'tfsCreateTime', 'projectId', 'bugId']
    bug_df = bug_df[columns]
    bug_df.set_index('bugId', inplace=True)
    # 快照数据 存储静态文件
    bugSnapshotData_filepath = Handle.bugSnapshotData_path
    bug_df['bugId'] = bug_df.index
    file_path = os.path.join(bugSnapshotData_filepath, f'{projectId}.pkl')
    bug_df.to_pickle(file_path)
    return

def resp_TaskQuery(taskId):
    try:
        result = TaskQuery(taskId)['result'][0]
        accomplishTime = result['accomplishTime']
        return accomplishTime
    except Exception as e:
        Log.error(f"resp_TaskQuery is except:\n{e}")
        return